import openpyxl

# workbook = openpyxl.Workbook("work.xlsx")
# workbook.create_sheet("data")
# workbook.save("work.xlsx")

# workbook = openpyxl.Workbook("info.xlsx")
# workbook.create_sheet("infomation")
# workbook.save("info.xlsx")

#
# # row = [["第一列","第二列","第三列","第四列","第五列","第六列","第七列","第八列","第九列","第十列",]]
# # rows = []
# # for i in range(2000):
# #     rows = []
# #     for j in range(10):
# #         rows.append(j)
# #     row.append(rows)
# # # print(row)
# #
# # sheet = workbook["data"]
# # for i in row:
# #     sheet.append(i)
# # workbook.save("work.xlsx")
#
#
# for i in sheet.rows:
#     for j in i:
#         print(j.value,end="\t\t")
#     print()



# workbook = openpyxl.load_workbook("info.xlsx")
# workbook.create_sheet("")
# workbook.remove(workbook["infomation"])
# workbook.save("info.xlsx")




# workbook = openpyxl.Workbook("info.xlsx")
# workbook.create_sheet("user_info")
# workbook.save("info.xlsx")


workbook = openpyxl.load_workbook("info.xlsx")
sheet = workbook["user_info"]
insert_list = []
# in_tuple = ()

for i in sheet[2:2001]:
    to_list = []
    for j in i:
        to_list.append(j.value)
    insert_list.append(tuple(to_list))

# print(insert_list)
# print(insert_tuple)

# print(insert_list)
        # print(j.value,end="\t")
    # print()

# print(insert_list)

# row_list = ["id","idcard","username","realname","pwd","telphone","email","age","sex",
#              "address","hiredate","sal","job","company"]
# info_list = [1,123456,"张三","张义山","123456","1399696","163.com",15,"男","安徽省合肥市","2002-2-5",18000,"工程师","网易"]
#
# # sheet.append(row_list)
# for i in range(2000):
#     sheet.append(info_list)
# workbook.save("info.xlsx")

# for i in sheet.rows:
#     for j in i:
#         print(j.value,end="\t\t")
#     print()


import pymysql

db = pymysql.connect(
    host= 'localhost',
    port=3306,
    database="hrjt",
    password="123456",
    user="root",
    charset="utf8"
)

# insert_list = []
# # in_tuple = ()
#
# for i in sheet[2:2001]:
#     to_list = []
#     for j in i:
#         to_list.append(j.value)
#     insert_list.append(tuple(to_list))

try:
    with db.cursor() as cursor:
        sql = "insert into user_info values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s);"
        data = ()
        cursor.executemany(sql,insert_list)

except Exception:
    print("插入数据错误")
db.commit()
# # print(data)
db.close()
